package hospital.daoImpl;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.type.StandardBasicTypes;
import org.springframework.web.context.request.RequestAttributes;
import org.springframework.web.context.request.RequestContextHolder;

import hospital.dao.LegalDao;
import hospital.model.AuditDetailModel;
import hospital.model.EmpInfoModel;
import hospital.model.LoginModel;
import hospital.model.RecordDetailModel;
import hospital.util.HospitalUtil;

public class LegalDaoImpl implements LegalDao {
	private static final Logger log = Logger.getLogger(LegalDaoImpl.class);
	private SessionFactory sessionFactory;
	private HospitalUtil hospitalUtil;

	public HospitalUtil getHospitalUtil() {
		return hospitalUtil;
	}

	public void setHospitalUtil(HospitalUtil hospitalUtil) {
		this.hospitalUtil = hospitalUtil;
	}

	public void setSessionFactory(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public List<EmpInfoModel> selectGetDocResult(long userId, String firstname, String lastname) {
		Criteria cr = sessionFactory.getCurrentSession().createCriteria(EmpInfoModel.class);
		cr.add(Restrictions.eq("role", (byte) 1));
		if (userId != -1) {
			cr.add(Restrictions.eq("userId", userId));
		}
		if (!firstname.equals("")) {
			cr.add(Restrictions.eq("firstname", firstname));
		}
		if (!lastname.equals("")) {
			cr.add(Restrictions.eq("lastname", lastname));
		}
		cr.addOrder(Order.asc("userId"));
		try {
			return (List<EmpInfoModel>) cr.list();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<AuditDetailModel> selectGetAuditDetailList(long docId) {
		try {
			String sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
					+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
					+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
					+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
					+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D "
					+ "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F " + "ON E.REV=F.REV " + "ORDER BY REV";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId",
					docId));
			sqlQuery.addScalar("RECORD_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("REV", StandardBasicTypes.INTEGER);
			sqlQuery.addScalar("REVTYPE", StandardBasicTypes.BYTE);
			sqlQuery.addScalar("REVTSTMP", StandardBasicTypes.LONG);
			sqlQuery.addScalar("OPERATOR", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("START_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("END_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DIAGNOSIS", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DRUG", StandardBasicTypes.STRING);
			sqlQuery.addScalar("INSTRUCTION", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DEA_NUM", StandardBasicTypes.STRING);
			sqlQuery.addScalar("TREATMENT_SCHE", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_COMMENT", StandardBasicTypes.STRING);
			List<AuditDetailModel> auditDetails = new ArrayList<AuditDetailModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				AuditDetailModel auditDetail = new AuditDetailModel();
				Object[] o = it.next();
				auditDetail.setRecordId((long) o[0]);
				auditDetail.setRev((int) o[1]);
				auditDetail.setRevType((byte) o[2]);
				auditDetail.setRevTstmp((long) o[3]);
				auditDetail.setOperator((long) o[4]);
				auditDetail.setPatientId((long) o[5]);
				auditDetail.setDoctorId((long) o[6]);
				auditDetail.setStartTime((long) o[7]);
				auditDetail.setEndTime((long) o[8]);
				auditDetail.setPatientName((String) o[9] + " " + (String) o[10]);
				auditDetail.setDoctorName((String) o[11] + " " + (String) o[12]);
				auditDetail.setDiagnosis((String) o[13]);
				auditDetail.setDrug((String) o[14]);
				auditDetail.setInstruction((String) o[15]);
				auditDetail.setDeaNum((String) o[16]);
				auditDetail.setTreatmentSchedule((String) o[17]);
				auditDetail.setComment((String) o[18]);
				auditDetails.add(auditDetail);
			}
			return auditDetails;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public List<AuditDetailModel> selectSearchAudit(long docId, long patientId, long recordTimeStart, long recordTimeEnd) {
		try {
			String sql = "";
			SQLQuery sqlQuery = null;
			int count = 0;
			if (patientId != -1) {
				count += 1;
			}
			if (recordTimeStart != -1) {
				count += 2;
			}
			if (recordTimeEnd != -1) {
				count += 4;
			}
			switch (count) {
			case 0:
				return selectGetAuditDetailList(docId);
			case 1:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId AND PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV " + "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("patientId", patientId));
				break;
			case 2:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D "
						+ "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP>= :recordTimeStart " + "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("recordTimeStart", recordTimeStart));
				break;
			case 3:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId AND PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP>= :recordTimeStart " + "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("patientId", patientId).setLong("recordTimeStart", recordTimeStart));
				break;
			case 4:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D "
						+ "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP< :recordTimeEnd " + "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 5:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId AND PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP< :recordTimeEnd " + "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd));
				break;
			case 6:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId) AS A " + "INNER JOIN USER_INFO AS B "
						+ "ON A.PATIENT_ID=B.USER_ID) AS C " + "INNER JOIN USER_INFO AS D "
						+ "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP< :recordTimeEnd AND REVTSTMP>= :recordTimeStart "
						+ "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("recordTimeEnd", recordTimeEnd).setLong("recordTimeStart", recordTimeStart));
				break;
			case 7:
				sql = "SELECT E.*, F.REVTSTMP, F.OPERATOR FROM "
						+ "(SELECT C.*, D.FIRST_NAME AS DOC_FIRSTNAME, D.LAST_NAME AS DOC_LASTNAME FROM "
						+ "(SELECT A.*, B.FIRST_NAME AS PATIENT_FIRSTNAME, B.LAST_NAME AS PATIENT_LASTNAME FROM "
						+ "(SELECT * FROM VISIT_RECORD_AUD WHERE DOC_ID= :docId AND PATIENT_ID= :patientId) AS A "
						+ "INNER JOIN USER_INFO AS B " + "ON A.PATIENT_ID=B.USER_ID) AS C "
						+ "INNER JOIN USER_INFO AS D " + "ON C.DOC_ID=D.USER_ID) AS E " + "INNER JOIN REVINFO AS F "
						+ "ON E.REV=F.REV AND REVTSTMP< :recordTimeEnd AND REVTSTMP>= :recordTimeStart "
						+ "ORDER BY REV";
				sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("docId", docId)
						.setLong("patientId", patientId).setLong("recordTimeEnd", recordTimeEnd).setLong(
						"recordTimeStart", recordTimeStart));
				break;
			default:
				break;
			}
			sqlQuery.addScalar("RECORD_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("REV", StandardBasicTypes.INTEGER);
			sqlQuery.addScalar("REVTYPE", StandardBasicTypes.BYTE);
			sqlQuery.addScalar("REVTSTMP", StandardBasicTypes.LONG);
			sqlQuery.addScalar("OPERATOR", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("DOC_ID", StandardBasicTypes.LONG);
			sqlQuery.addScalar("START_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("END_TIME", StandardBasicTypes.LONG);
			sqlQuery.addScalar("PATIENT_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("PATIENT_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_FIRSTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_LASTNAME", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DIAGNOSIS", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DRUG", StandardBasicTypes.STRING);
			sqlQuery.addScalar("INSTRUCTION", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DEA_NUM", StandardBasicTypes.STRING);
			sqlQuery.addScalar("TREATMENT_SCHE", StandardBasicTypes.STRING);
			sqlQuery.addScalar("DOC_COMMENT", StandardBasicTypes.STRING);
			List<AuditDetailModel> auditDetails = new ArrayList<AuditDetailModel>();
			List<Object[]> result = (List<Object[]>) sqlQuery.list();
			Iterator<Object[]> it = result.iterator();
			while (it.hasNext()) {
				AuditDetailModel auditDetail = new AuditDetailModel();
				Object[] o = it.next();
				auditDetail.setRecordId((long) o[0]);
				auditDetail.setRev((int) o[1]);
				auditDetail.setRevType((byte) o[2]);
				auditDetail.setRevTstmp((long) o[3]);
				auditDetail.setOperator((long) o[4]);
				auditDetail.setPatientId((long) o[5]);
				auditDetail.setDoctorId((long) o[6]);
				auditDetail.setStartTime((long) o[7]);
				auditDetail.setEndTime((long) o[8]);
				auditDetail.setPatientName((String) o[9] + " " + (String) o[10]);
				auditDetail.setDoctorName((String) o[11] + " " + (String) o[12]);
				auditDetail.setDiagnosis((String) o[13]);
				auditDetail.setDrug((String) o[14]);
				auditDetail.setInstruction((String) o[15]);
				auditDetail.setDeaNum((String) o[16]);
				auditDetail.setTreatmentSchedule((String) o[17]);
				auditDetail.setComment((String) o[18]);
				auditDetails.add(auditDetail);
			}
			return auditDetails;
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public String selectCheckPassword() {
		try {
			RequestAttributes ra = RequestContextHolder.getRequestAttributes();
			long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
			String sql = "SELECT B.PASSWORD FROM " + "(SELECT * FROM USER_INFO WHERE USER_ID= :userId) AS A "
					+ "INNER JOIN LOGIN AS B " + "ON A.EMAIL=B.USERNAME";
			SQLQuery sqlQuery = (SQLQuery) (sessionFactory.getCurrentSession().createSQLQuery(sql).setLong("userId",
					currentUserId));
			sqlQuery.addScalar("PASSWORD", StandardBasicTypes.STRING);
			return (String) sqlQuery.uniqueResult();
		} catch (HibernateException e) {
			e.printStackTrace();
			return null;
		}
	}

	@Override
	public boolean updateChangePassword(String password) {
		try {
			Session session = sessionFactory.getCurrentSession();
			Criteria cr = session.createCriteria(EmpInfoModel.class);
			RequestAttributes ra = RequestContextHolder.getRequestAttributes();
			long currentUserId = (long) ra.getAttribute("currentUserId", RequestAttributes.SCOPE_SESSION);
			cr.add(Restrictions.eq("userId", currentUserId));

			if (cr.uniqueResult() != null) {
				EmpInfoModel empInfo = (EmpInfoModel) cr.uniqueResult();
				LoginModel login = (LoginModel) session.get(LoginModel.class, empInfo.getEmail());
				login.setPassword(hospitalUtil.encrypt(password));
				session.update(login);
				return true;
			} else {
				return false;
			}
		} catch (HibernateException e) {
			e.printStackTrace();
			return false;
		}
	}
}
